package com.controller;

import com.pojo.Contact;

import java.sql.*;
import java.util.LinkedList;
import java.util.List;

public class ContactController extends Controller{

    public ContactController() {
    }

    /**
     * @return 所有联系人信息的列表
     */
    public List<Contact> findAll() {
        try {
            List<Contact> contacts = new LinkedList<>();
            Connection conn = null;
            Statement stmt = null;
            //STEP 2: Register JDBC driver
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            stmt = conn.createStatement();
            String sql;
            sql = "SELECT * FROM contact";
            ResultSet rs = stmt.executeQuery(sql);
            while (rs.next()) {
                Contact contact = new Contact();
                contact.setName(rs.getString("name"));
                contact.setPhone(rs.getString("phone"));
                contact.setGender(rs.getString("gender"));
                contact.setAddress(rs.getString("address"));
                contact.setEmail(rs.getString("email"));
                contact.setGroup(rs.getString("group"));
                contact.setStar(rs.getBoolean("isStar"));
                contacts.add(contact);
            }
            //STEP 6: Clean-up environment
            rs.close();
            stmt.close();
            conn.close();
            return contacts;
        }catch (SQLException se){
            se.printStackTrace();
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 按姓名查找
     * @param name 姓名
     * @return 查询结果列表
     */
    public List<Contact> findByName(String name){
        try {
            List<Contact> contacts = new LinkedList<>();
            Connection conn = null;
            Statement stmt = null;
            //STEP 2: Register JDBC driver
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            stmt = conn.createStatement();
            String sql;
            sql = "SELECT * FROM contact WHERE `name` LIKE ?" ;
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%"+name+"%");
            pstmt.execute();
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Contact contact = new Contact();
                contact.setName(rs.getString("name"));
                contact.setPhone(rs.getString("phone"));
                contact.setGender(rs.getString("gender"));
                contact.setAddress(rs.getString("address"));
                contact.setEmail(rs.getString("email"));
                contact.setGroup(rs.getString("group"));
                contact.setStar(rs.getBoolean("isStar"));
                contacts.add(contact);
            }
            //STEP 6: Clean-up environment
            rs.close();
            stmt.close();
            conn.close();
            return contacts;
        }catch (SQLException se){
            se.printStackTrace();
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 按电话号码查找
     * @param phone 电话号码（字符串）
     * @return 查询结果列表
     */
    public List<Contact> findByPhone(String phone) {
        try {
            List<Contact> contacts = new LinkedList<>();
            Connection conn = null;
            Statement stmt = null;
            //STEP 2: Register JDBC driver
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            stmt = conn.createStatement();
            String sql;
            sql = "SELECT * FROM contact WHERE `phone` LIKE ?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%"+phone+"%");
            pstmt.execute();
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Contact contact = new Contact();
                contact.setName(rs.getString("name"));
                contact.setPhone(rs.getString("phone"));
                contact.setGender(rs.getString("gender"));
                contact.setAddress(rs.getString("address"));
                contact.setEmail(rs.getString("email"));
                contact.setGroup(rs.getString("group"));
                contact.setStar(rs.getBoolean("isStar"));
                contacts.add(contact);
            }
            //STEP 6: Clean-up environment
            rs.close();
            stmt.close();
            conn.close();
            return contacts;
        }catch (SQLException se){
            se.printStackTrace();
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 按分组查找
     * @param group 分组名
     * @return 查询结果列表
     */
    public List<Contact> findByGroup(String group) {
        try {
            List<Contact> contacts = new LinkedList<>();
            Connection conn = null;
            Statement stmt = null;
            //STEP 2: Register JDBC driver
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            stmt = conn.createStatement();
            String sql;
            sql = "SELECT * FROM contact WHERE `group` = ?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, group);
            pstmt.execute();
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Contact contact = new Contact();
                contact.setName(rs.getString("name"));
                contact.setPhone(rs.getString("phone"));
                contact.setGender(rs.getString("gender"));
                contact.setAddress(rs.getString("address"));
                contact.setEmail(rs.getString("email"));
                contact.setGroup(rs.getString("group"));
                contact.setStar(rs.getBoolean("isStar"));
                contacts.add(contact);
            }
            //STEP 6: Clean-up environment
            rs.close();
            stmt.close();
            conn.close();
            return contacts;
        } catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 同时按姓名、分组查找
     * @param name 姓名
     * @param group 分组
     * @return 查询结果列表
     */
    public List<Contact> findByNameAndGroup(String name, String group) {
        try {
            List<Contact> contacts = new LinkedList<>();
            Connection conn = null;
            Statement stmt = null;
            //STEP 2: Register JDBC driver
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            stmt = conn.createStatement();
            String sql;
            sql = "SELECT * FROM contact WHERE name LIKE ? AND `group` = ?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, "%"+name+"%");
            pstmt.setString(2, group);
            pstmt.execute();
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Contact contact = new Contact();
                contact.setName(rs.getString("name"));
                contact.setPhone(rs.getString("phone"));
                contact.setGender(rs.getString("gender"));
                contact.setAddress(rs.getString("address"));
                contact.setEmail(rs.getString("email"));
                contact.setGroup(rs.getString("group"));
                contact.setStar(rs.getBoolean("isStar"));
                contacts.add(contact);
            }
            //STEP 6: Clean-up environment
            rs.close();
            stmt.close();
            conn.close();
            return contacts;
        } catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }

    /**
     * 添加新的联系人
     * @param person 新的联系人信息
     * @return 添加是否成功
     */
    public boolean addPerson(Contact person) {
        try {
            Connection conn = null;
            Statement stmt = null;
            //STEP 2: Register JDBC driver
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            stmt = conn.createStatement();
            String sql;
            sql = ("INSERT INTO contact" + "(name, phone, gender, address, email, `group`, isStar) VALUES (?,?,?,?,?,?,?)");
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, person.getName());
            pstmt.setString(2, person.getPhone());
            pstmt.setString(3, person.getGender());
            pstmt.setString(4, person.getAddress());
            pstmt.setString(5, person.getEmail());
            pstmt.setString(6, person.getGroup());
            pstmt.setBoolean(7, person.isStar());
            pstmt.execute();
            stmt.close();
            conn.close();
        }catch (SQLException se){
            se.printStackTrace();
            return false;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /**
     * 删除联系人
     * @param contact 待删除的联系人信息
     * @return 删除是否成功
     */
    public boolean deletePerson(Contact contact) {
        try {
            Connection conn = null;
            Statement stmt = null;
            //STEP 2: Register JDBC driver
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL,USER,PASS);
            stmt = conn.createStatement();
            String sql;
            sql = ("DELETE FROM contact WHERE phone = ?");
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, contact.getPhone());
            pstmt.execute();
            stmt.close();
            conn.close();
        }catch (SQLException se){
            se.printStackTrace();
            return false;
        }catch (Exception e){
            e.printStackTrace();
            return false;
        }
        return true;
    }

    /**
     * 修改表格中选中的联系人
     * @param oldContact 选中待修改的联系人
     * @param newContact 新的联系人信息
     * @return 修改是否成功
     */
    public boolean modifyPerson(Contact oldContact, Contact newContact) {
        try {
            Connection conn = null;
            Statement stmt = null;
            //STEP 2: Register JDBC driver
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            stmt = conn.createStatement();
            String sql;
            sql = ("UPDATE contact SET name = ?, phone = ?, gender = ?, address = ?, email = ?, `group` = ?, isStar = ? WHERE phone = ?" );
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, newContact.getName());
            pstmt.setString(2, newContact.getPhone());
            pstmt.setString(3, newContact.getGender());
            pstmt.setString(4, newContact.getAddress());
            pstmt.setString(5, newContact.getEmail());
            pstmt.setString(6, newContact.getGroup());
            pstmt.setBoolean(7, newContact.isStar());
            pstmt.setString(8, oldContact.getPhone());
            pstmt.execute();
            stmt.close();
            conn.close();
        }catch (SQLException se){
            se.printStackTrace();
        }catch (Exception e){
            e.printStackTrace();
        }
        return true;
    }

    public List<Contact> findByPhoneAccurate(String phone) {
        try {
            List<Contact> contacts = new LinkedList<>();
            Connection conn = null;
            Statement stmt = null;
            //STEP 2: Register JDBC driver
            Class.forName(JDBC_DRIVER);
            conn = DriverManager.getConnection(DB_URL, USER, PASS);
            stmt = conn.createStatement();
            String sql;
            sql = "SELECT * FROM contact WHERE `phone` = ?";
            PreparedStatement pstmt = conn.prepareStatement(sql);
            pstmt.setString(1, phone);
            pstmt.execute();
            ResultSet rs = pstmt.executeQuery();
            while (rs.next()) {
                Contact contact = new Contact();
                contact.setName(rs.getString("name"));
                contact.setPhone(rs.getString("phone"));
                contact.setGender(rs.getString("gender"));
                contact.setAddress(rs.getString("address"));
                contact.setEmail(rs.getString("email"));
                contact.setGroup(rs.getString("group"));
                contact.setStar(rs.getBoolean("isStar"));
                contacts.add(contact);
            }
            //STEP 6: Clean-up environment
            rs.close();
            stmt.close();
            conn.close();
            return contacts;
        }catch (SQLException se){
            se.printStackTrace();
        }catch (Exception e){
            e.printStackTrace();
        }
        return null;
    }
}
